package Common

import (
	"fmt"
	"strconv"
	"strings"
)

type SqlMaker struct {
	field []string
	table []string
	insertOrUpdateField []string
	insertOrUpdateValue []string
	insertOrUpdateValueReal []string
	where []string
	whereValue []string
	order []string
	page string
	pageSize string
	limit string
}

func NewSqlMaker() *SqlMaker {
	return new(SqlMaker)
}

func (i *SqlMaker) InSlice(value string, list []string) bool {
	for _, v := range list {
		if v == value {
			return true
			break
		}
	}
	return false
}

func (i *SqlMaker) String2Int64(theString string) int64 {
	theInt64, _ := strconv.ParseInt(theString, 10, 64)
	return theInt64
}

func (i *SqlMaker) SetField(field string) {
	i.field = append(i.field, field)
}

func (i *SqlMaker) SetTable(table string) {
	i.table = append(i.table, table)
}

func (i *SqlMaker) GetTable() string {
	return strings.Join(i.table, ",")
}

func (i *SqlMaker) Set(field, value string) {
	i.insertOrUpdateField = append(i.insertOrUpdateField, field)
	i.insertOrUpdateValue = append(i.insertOrUpdateValue, "?")
	i.insertOrUpdateValueReal = append(i.insertOrUpdateValueReal, value)
}

func (i *SqlMaker) Where(field, operator, value string) {
	if i.InSlice(operator, []string{"<", "<=", "=", ">=", ">", "<>"}) {
		i.where = append(i.where, fmt.Sprintf("%s %s ?", field, operator))
		i.whereValue = append(i.whereValue, value)
	} else if i.InSlice(operator, []string{"like", "not like"}) {
		i.where = append(i.where, fmt.Sprintf("%s %s '%?%'", field, operator))
		i.whereValue = append(i.whereValue, value)
	} else if i.InSlice(operator, []string{"between and"}) {
		temp := strings.Split(value, ",")
		i.where = append(i.where, fmt.Sprintf("%s between ? and ?", field))
		i.whereValue = append(i.whereValue, temp[0])
		i.whereValue = append(i.whereValue, temp[1])
	} else if i.InSlice(operator, []string{"contain"}) {
		i.where = append(i.where, fmt.Sprintf("FIND_IN_SET(?,%s)>0", field))
		i.whereValue = append(i.whereValue, value)
	} else if i.InSlice(operator, []string{"not contain"}) {
		i.where = append(i.where, fmt.Sprintf("FIND_IN_SET(?,%s)<=0", field))
		i.whereValue = append(i.whereValue, value)
	} else if i.InSlice(operator, []string{"match word"}) {
		i.where = append(i.where, fmt.Sprintf("%s REGEXP '[[:<:]]?[[:>:]]'", field))
		i.whereValue = append(i.whereValue, value)
	} else if i.InSlice(operator, []string{"in", "not in"}) {
		i.where = append(i.where, fmt.Sprintf("%s %s (?)", field, operator))
		i.whereValue = append(i.whereValue, value)
	}
}

func (i *SqlMaker) AndWhere(field, operator, value string) {
	i.WhereExpression("and")
	i.Where(field, operator, value)
}

func (i *SqlMaker) OrWhere(field, operator, value string) {
	i.WhereExpression("or")
	i.Where(field, operator, value)
}

func (i *SqlMaker) WhereExpression(operator string) {
	switch operator {
	case "(":
		i.where = append(i.where, "(")
	case ")":
		i.where = append(i.where, ")")
	case "and":
		i.where = append(i.where, " and ")
	case "or":
		i.where = append(i.where, " or ")
	default:
		break
	}
}

func (i *SqlMaker) WhereExpressions(operators []string) {
	for _, operator := range operators {
		i.WhereExpression(operator)
	}
}

func (i *SqlMaker) SetOrder(field, method string) {
	i.order = append(i.order, fmt.Sprintf("%s %s", field, method))
}

func (i *SqlMaker) SetPage(page string) {
	i.page = page
}

func (i *SqlMaker) GetPage() string {
	return i.page
}

func (i *SqlMaker) SetPageSize(pageSize string) {
	i.pageSize = pageSize
}

func (i *SqlMaker) GetPageSize() string {
	return i.pageSize
}

func (i *SqlMaker) SetLimit() {
	page := i.String2Int64(i.GetPage())
	pageSize := i.String2Int64(i.GetPageSize())
	start := (page - 1) * pageSize
	i.limit = fmt.Sprintf("limit %d,%d", start, pageSize)
}

func (i *SqlMaker) GetWhereSql() string {
	var where string
	if len(i.where) > 0 {
		where = fmt.Sprintf("where %s", strings.Join(i.where, ""))
	}
	return where
}

func (i *SqlMaker) GetSelectSql() (string, []string) {
	var field, table, where, order string
	where = i.GetWhereSql()
	if len(i.order) > 0 {
		order = fmt.Sprintf("order by %s", strings.Join(i.order, ","))
	}
	field = strings.Join(i.field, ",")
	table = i.GetTable()
	sql := fmt.Sprintf("select %s from %s %s %s %s", field, table, where, order, i.limit)
	placeholder := i.whereValue
	return sql, placeholder
}

func (i *SqlMaker) GetCountSql() (string, []string) {
	var table, where string
	where = i.GetWhereSql()
	table = i.GetTable()
	sql := fmt.Sprintf("select count(*) as total from %s %s", table, where)
	placeholder := i.whereValue
	return sql, placeholder
}

func (i *SqlMaker) GetInsertSql() (string, []string) {
	var table, insertOrUpdateField, insertOrUpdateValue string
	table = i.GetTable()
	insertOrUpdateField = strings.Join(i.insertOrUpdateField, ",")
	insertOrUpdateValue = strings.Join(i.insertOrUpdateValue, ",")
	sql := fmt.Sprintf("insert into %s (%s) values(%s)", table, insertOrUpdateField, insertOrUpdateValue)
	placeholder := i.insertOrUpdateValueReal
	return sql, placeholder
}

func (i *SqlMaker) GetUpdateSql() (string, []string) {
	var table, where string
	table = i.GetTable()
	var update []string
	for _, field := range i.insertOrUpdateField {
		update = append(update, fmt.Sprintf("%s=?", field))
	}
	updateString := strings.Join(update, ",")
	where = i.GetWhereSql()
	sql := fmt.Sprintf("update %s set %s %s", table, updateString, where)
	placeholder := append(i.insertOrUpdateValueReal, i.whereValue...)
	return sql, placeholder
}

func (i *SqlMaker) GetDeleteSql() (string, []string) {
	var table, where string
	table = i.GetTable()
	where = i.GetWhereSql()
	sql := fmt.Sprintf("delete from %s %s", table, where)
	placeholder := i.whereValue
	return sql, placeholder
}